﻿
-- 生成加班类型数据
--USE [scm_main]
GO
/****** Object:  Table [dbo].[bd_sys_item]    Script Date: 2020/1/7 8:36:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[bd_sys_item](
	[id] [int] NULL,
	[bh] [varchar](50) NULL,
	[type_id] [int] NULL,
	[name] [varchar](200) NULL
) ON [PRIMARY]

GO
INSERT [dbo].[bd_sys_item] ([id], [bh], [type_id], [name]) VALUES (1, N'1000001', 38, N'平时加班')
INSERT [dbo].[bd_sys_item] ([id], [bh], [type_id], [name]) VALUES (2, N'1000002', 38, N'周末')
INSERT [dbo].[bd_sys_item] ([id], [bh], [type_id], [name]) VALUES (3, N'1000003', 38, N'法定节假日加班')
INSERT [dbo].[bd_sys_item] ([id], [bh], [type_id], [name]) VALUES (4, N'1000004', 38, N'加班转调休')
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'自增ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bd_sys_item', @level2type=N'COLUMN',@level2name=N'id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bd_sys_item', @level2type=N'COLUMN',@level2name=N'bh'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据类型ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bd_sys_item', @level2type=N'COLUMN',@level2name=N'type_id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据类型名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'bd_sys_item', @level2type=N'COLUMN',@level2name=N'name'
GO




-- 生成日期数据
DECLARE @i int=1,@Date varchar(10)='2018-01-01',@EndDate varchar(10)='2222-12-31'
WHILE(@Date<=@EndDate)
BEGIN
	PRINT @Date
  
  INSERT INTO bd_sys_dim_date(date_key,date,date_year,date_month, date_day, date_week, date_week_name)
  select CAST(CONVERT(VARCHAR(24),CAST(@Date AS DATE),112) AS INT),@Date,YEAR(@Date) ,MONTH(@Date),DAY(@Date),CASE datepart(weekday,@Date) WHEN 1 THEN 7 ELSE datepart(weekday,@Date)-1 END, datename(weekday,@Date)

	SET @Date=CONVERT(varchar(10),DATEADD(DAY,1,@Date),120) 
END
